package com.wap.framework.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;

import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

import com.wap.framework.model.Advertisement;
import com.wap.framework.model.Content;
import com.wap.framework.model.PageAd;
import com.wap.framework.pagination.PaginationHelper;

public class PageAdDAOImpl implements PageAdDAO {

	private JdbcTemplate jdbcTemplate;

	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}

	private static String countSql = "select count(*) from dbo.page_ad where delflag=0";
	private static String listCurrentPage = "select * from (select a.id as pageId,a.title as pageTitle,c.id as adId,c.title as adTitle,c.type as adType,b.id,b.layout,b.create_time,row_number() over(order by b.id) as rownum from dbo.dyn_page a,dbo.page_ad b,dbo.wap_ad c where b.page_id=a.id and b.ad_id=c.id and b.delflag=0) as t where t.rownum>? and t.rownum<=?";
	private static String listPageAd = "select a.id as pageId,a.title as pageTitle,c.id as adId,c.title as adTitle,c.type as adType,b.id,b.layout,b.create_time from dbo.dyn_page a,dbo.page_ad b,dbo.wap_ad c where b.page_id=a.id and b.ad_id=c.id and b.delflag=0";
	private static String editPageAd = "select a.id as pageId,a.title as pageTitle,c.id as adId,c.title as adTitle,c.type as adType,b.id,b.layout,b.create_time from dbo.dyn_page a,dbo.page_ad b,dbo.wap_ad c where b.page_id=a.id and b.ad_id=c.id and b.id=?";
	private static String insertAdToPage = "insert into dbo.page_ad(page_id,ad_id,layout,create_time) values(?,?,?,getdate())";
	private static String updatePageAd = "update dbo.page_ad set page_id=?,ad_id=?,layout=? where id=?";
	private static String deletePageAd = "update dbo.page_ad set delflag=1 where id=?";
	private static String listContent = "select id,title from dbo.dyn_page where delflag=0";
	private static String listAdvertisement = "select id,title,type from dbo.wap_ad where alive=0 and delflag=0";

	public void insertAdToPage(PageAd pageAd) {
		Object[] params = new Object[] { pageAd.getPageId(), pageAd.getAdId(),
				pageAd.getLayout() == 0 ? "H" : "F" };
		int[] types = new int[] { Types.INTEGER, Types.INTEGER, Types.VARCHAR };

		this.jdbcTemplate.update(insertAdToPage, params, types);
	}

	@SuppressWarnings("unchecked")
	public List<Object> listPageAd(int pageNum) {
		PaginationHelper ph = new PaginationHelper(pageNum);
		ph.setJdbcTemplate(jdbcTemplate);
		ph.init(countSql);

		Object[] params = new Object[] { ph.getStartIndex(), ph.getEndIndex() };
		int[] types = new int[] { Types.INTEGER, Types.INTEGER };
		List<PageAd> pageAds = jdbcTemplate.query(listCurrentPage, params,
				types, new PageAdRowMapper());
		
		List result = new ArrayList(2);
		result.add(pageAds);
		result.add(ph);
		
		return result;
	}

	public void updatePageAd(PageAd pageAd) {
		Object[] params = new Object[] { pageAd.getPageId(), pageAd.getAdId(),
				pageAd.getLayout() == 0 ? "H" : "F", pageAd.getId() };
		int[] types = new int[] { Types.INTEGER, Types.INTEGER, Types.VARCHAR,
				Types.INTEGER };

		this.jdbcTemplate.update(updatePageAd, params, types);
	}

	public void deletePageAd(final int[] ids) {
		BatchPreparedStatementSetter batchPstmt = new BatchPreparedStatementSetter() {
			public int getBatchSize() {
				return ids.length;
			}

			public void setValues(PreparedStatement pstmt, int i)
					throws SQLException {
				pstmt.setInt(1, ids[i]);
			}
		};
		this.jdbcTemplate.batchUpdate(deletePageAd, batchPstmt);
	}

	@SuppressWarnings("unchecked")
	public PageAd editPageAd(int id) {
		Object[] params = new Object[] { id };
		int[] types = new int[] { Types.INTEGER };
		List<PageAd> pageAd = jdbcTemplate.query(editPageAd, params, types,
				new PageAdRowMapper());
		if (pageAd == null || pageAd.size() == 0) {
			return null;
		}
		return (PageAd) pageAd.get(0);
	}

	public List[] load() {
		List[] lists = { new ArrayList<Content>(),
				new ArrayList<Advertisement>() };

		lists[0] = jdbcTemplate.query(listContent, new ContentRowMapper());
		if (lists[0] == null || lists[0].size() == 0) {
			lists[0] = null;
		}

		lists[1] = jdbcTemplate.query(listAdvertisement,
				new AdvertisementRowMapper());
		if (lists[1] == null || lists[1].size() == 0) {
			lists[1] = null;
		}

		return lists;
	}

	class PageAdRowMapper implements RowMapper {
		public Object mapRow(ResultSet rs, int index) throws SQLException {
			PageAd pageAd = new PageAd();
			pageAd.setId(rs.getInt("id"));
			pageAd.setPageId(rs.getInt("pageId"));
			pageAd.setPageTitle(rs.getString("pageTitle"));
			pageAd.setAdId(rs.getInt("adId"));
			pageAd.setAdTitle(rs.getString("adTitle"));
			pageAd.setAdType(rs.getInt("adType"));
			if (rs.getString("layout").trim().equals("H")) {
				pageAd.setLayout(new Integer(0));
			} else {
				pageAd.setLayout(new Integer(1));
			}
			pageAd.setCreateTime(rs.getTimestamp("create_time"));
			return pageAd;
		}
	}

	class ContentRowMapper implements RowMapper {
		public Object mapRow(ResultSet rs, int index) throws SQLException {
			Content content = new Content();
			content.setId(rs.getInt("id"));
			content.setTitle(rs.getString("title"));
			return content;
		}
	}

	class AdvertisementRowMapper implements RowMapper {
		public Object mapRow(ResultSet rs, int index) throws SQLException {
			Advertisement ad = new Advertisement();
			ad.setId(rs.getInt("id"));
			ad.setTitle(rs.getString("title"));
			ad.setType(rs.getInt("type"));
			return ad;
		}
	}
}
